<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>sql_alter_table: common_schema documentation</title>
	<meta name="description" content="sql_alter_table: common_schema" />
	<meta name="keywords" content="sql_alter_table: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="sql_alter_table.html">sql_alter_table</a></h2>	
<h3>NAME</h3>
sql_alter_table: Generate ALTER TABLE SQL statements per table, with engine and create options
<h3>TYPE</h3>
View

<h3>DESCRIPTION</h3>
<p></p>

<p><i>sql_alter_table</i> provides with SQL statements to alter a table to its current form.
</p>

<p>
	This view analyzes table structure and provides with the syntax required to rebuild the
	table by various aspects.
	It can be useful in generating a "resurrection" script to restore table to its current 
	engine or indexes state.
	For example, it may provide with the rollback script for a database migration from
	MyISAM to InnoDB, or from InnoDB Antelope to Barracuda format.
</p>

<h3>STRUCTURE</h3>

<blockquote><pre>
mysql&gt; DESC common_schema.sql_alter_table;
+-----------------+--------------+------+-----+---------+-------+
| Field           | Type         | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| TABLE_SCHEMA    | varchar(64)  | NO   |     |         |       |
| TABLE_NAME      | varchar(64)  | NO   |     |         |       |
| ENGINE          | varchar(64)  | YES  |     | NULL    |       |
| sql_drop_keys   | longtext     | YES  |     | NULL    |       |
| sql_add_keys    | longblob     | YES  |     | NULL    |       |
| table_options   | varchar(327) | YES  |     | NULL    |       |
| alter_statement | varchar(511) | YES  |     | NULL    |       |
+-----------------+--------------+------+-----+---------+-------+
</pre></blockquote>

<h3>SYNOPSIS</h3>

<p>Columns of this view:
	<ul>
		<li><strong>TABLE_SCHEMA</strong>: schema of current table</li>
		<li><strong>TABLE_NAME</strong>: current table name</li>
		<li><strong>ENGINE</strong>: current engine name</li>
		<li><strong>sql_drop_keys</strong>: 
			A SQL statement to drop all keys on table.
		</li>
		<li><strong>sql_add_keys</strong>: 
			A SQL statement to create all keys on table
		</li>
		<li><strong>table_options</strong>: 
			All table options for this table
		</li>
		<li><strong>alter_statement</strong>: 
			A SQL statement which ALTERs current table to its current engine with create options.
			<br/>Use <a href="eval.html">eval()</a> to apply SQL columns.
		</li>
		
	</ul>
</p>

<p>The SQL statements are not terminated by ';'.</p>

<h3>EXAMPLES</h3>
<p>Generate ALTER TABLE statements for `sakila` tables:</p>
<blockquote><pre>mysql&gt; SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, alter_statement FROM common_schema.sql_alter_table WHERE TABLE_SCHEMA='sakila';
+--------------+---------------+--------+-----------------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME    | ENGINE | alter_statement                                     |
+--------------+---------------+--------+-----------------------------------------------------+
| sakila       | actor         | InnoDB | ALTER TABLE `sakila`.`actor` ENGINE=InnoDB          |
| sakila       | address       | InnoDB | ALTER TABLE `sakila`.`address` ENGINE=InnoDB        |
| sakila       | category      | InnoDB | ALTER TABLE `sakila`.`category` ENGINE=InnoDB       |
| sakila       | city          | InnoDB | ALTER TABLE `sakila`.`city` ENGINE=InnoDB           |
| sakila       | country       | InnoDB | ALTER TABLE `sakila`.`country` ENGINE=InnoDB        |
| sakila       | customer      | InnoDB | ALTER TABLE `sakila`.`customer` ENGINE=InnoDB       |
| sakila       | film          | InnoDB | ALTER TABLE `sakila`.`film` ENGINE=InnoDB           |
| sakila       | film_actor    | InnoDB | ALTER TABLE `sakila`.`film_actor` ENGINE=InnoDB     |
| sakila       | film_category | InnoDB | ALTER TABLE `sakila`.`film_category` ENGINE=InnoDB  |
| sakila       | film_text     | MyISAM | ALTER TABLE `sakila`.`film_text` ENGINE=MyISAM      |
| sakila       | inventory     | InnoDB | ALTER TABLE `sakila`.`inventory` ENGINE=InnoDB      |
| sakila       | language      | InnoDB | ALTER TABLE `sakila`.`language` ENGINE=InnoDB       |
| sakila       | payment       | InnoDB | ALTER TABLE `sakila`.`payment` ENGINE=InnoDB        |
| sakila       | rental        | InnoDB | ALTER TABLE `sakila`.`rental` ENGINE=InnoDB         |
| sakila       | staff         | InnoDB | ALTER TABLE `sakila`.`staff` ENGINE=InnoDB          |
| sakila       | store         | InnoDB | ALTER TABLE `sakila`.`store` ENGINE=InnoDB          |
+--------------+---------------+--------+-----------------------------------------------------+
</pre></blockquote>

<p>Modify tables; again generate ALTER TABLE statements for `sakila` tables:</p>
<blockquote><pre>mysql&gt; ALTER TABLE sakila.film_text ENGINE=MyISAM ROW_FORMAT=FIXED;
mysql&gt; ALTER TABLE sakila.film_category ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

mysql&gt; SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE, alter_statement FROM common_schema.sql_alter_table WHERE TABLE_SCHEMA='sakila';
+--------------+---------------+--------+-------------------------------------------------------------------------------------------+
| TABLE_SCHEMA | TABLE_NAME    | ENGINE | alter_statement                                                                           |
+--------------+---------------+--------+-------------------------------------------------------------------------------------------+
| sakila       | actor         | InnoDB | ALTER TABLE `sakila`.`actor` ENGINE=InnoDB                                                |
| sakila       | address       | InnoDB | ALTER TABLE `sakila`.`address` ENGINE=InnoDB                                              |
| sakila       | category      | InnoDB | ALTER TABLE `sakila`.`category` ENGINE=InnoDB                                             |
| sakila       | city          | InnoDB | ALTER TABLE `sakila`.`city` ENGINE=InnoDB                                                 |
| sakila       | country       | InnoDB | ALTER TABLE `sakila`.`country` ENGINE=InnoDB                                              |
| sakila       | customer      | InnoDB | ALTER TABLE `sakila`.`customer` ENGINE=InnoDB                                             |
| sakila       | film          | InnoDB | ALTER TABLE `sakila`.`film` ENGINE=InnoDB                                                 |
| sakila       | film_actor    | InnoDB | ALTER TABLE `sakila`.`film_actor` ENGINE=InnoDB                                           |
| sakila       | film_category | InnoDB | ALTER TABLE `sakila`.`film_category` ENGINE=InnoDB row_format=COMPRESSED KEY_BLOCK_SIZE=8 |
| sakila       | film_text     | MyISAM | ALTER TABLE `sakila`.`film_text` ENGINE=MyISAM row_format=FIXED                           |
| sakila       | inventory     | InnoDB | ALTER TABLE `sakila`.`inventory` ENGINE=InnoDB                                            |
| sakila       | language      | InnoDB | ALTER TABLE `sakila`.`language` ENGINE=InnoDB                                             |
| sakila       | payment       | InnoDB | ALTER TABLE `sakila`.`payment` ENGINE=InnoDB                                              |
| sakila       | rental        | InnoDB | ALTER TABLE `sakila`.`rental` ENGINE=InnoDB                                               |
| sakila       | staff         | InnoDB | ALTER TABLE `sakila`.`staff` ENGINE=InnoDB                                                |
| sakila       | store         | InnoDB | ALTER TABLE `sakila`.`store` ENGINE=InnoDB                                                |
+--------------+---------------+--------+-------------------------------------------------------------------------------------------+
</pre></blockquote>
<p>Note again that the SQL statements are not terminated by ';'. 
Either CONCAT() these beforehand, or use <i>sed</i>/<i>awk</i> afterwards.</p>


<p>Show indexes DROP and ADD statements for a given table:</p>
<blockquote><pre>mysql&gt; SELECT TABLE_SCHEMA, TABLE_NAME, sql_drop_keys, sql_add_keys FROM common_schema.sql_alter_table WHERE TABLE_SCHEMA='sakila' and TABLE_NAME='rental' \G

 TABLE_SCHEMA: sakila
   TABLE_NAME: rental
sql_drop_keys: DROP KEY `idx_fk_customer_id`, DROP KEY `idx_fk_inventory_id`, DROP KEY `idx_fk_staff_id`, DROP PRIMARY KEY, DROP KEY `rental_date`
 sql_add_keys: ADD KEY `idx_fk_customer_id`(`customer_id`), ADD KEY `idx_fk_inventory_id`(`inventory_id`), ADD KEY `idx_fk_staff_id`(`staff_id`), ADD PRIMARY KEY (`rental_id`), ADD UNIQUE KEY `rental_date`(`rental_date`,`inventory_id`,`customer_id`)
</pre></blockquote>

<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer

<h3>SEE ALSO</h3>
<a href="eval.html">eval()</a>,
<a href="sql_foreign_keys.html">sql_foreign_keys</a>,
<a href="sql_range_partitions.html">sql_range_partitions</a>
<h3>AUTHOR</h3>
Shlomi Noach
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
